﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Net.Mail;
using System.Net;
using Ebiz.libs;
using System.Globalization;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Services;
using System.Web.Services;
using System.Collections.Specialized;
using System.IO;

namespace Ebiz
{
    public partial class EditRequest : System.Web.UI.Page
    {
        public string Test = "";

        public string DistributorList = "";
        public string ChannelList = "";
        public string TipeDiskon = "";
        public string InitDateStart = "";
        public string InitDateEnd = "";
        public enum ShowMode{
            _Customer, _Product
        }
        public enum EmailEngine{
            CDOSYS, NETMAIL
        }
        public enum EmailMode{
            REQUEST, APPROVED, RETURNED, REJECTED
        }

        public string CLNumber = "";
        public string RequestCustomers = "";
        public string RequestProducts = "";
        public string RequestFiles = "";
        public string RequestApprovals = "";
        public string PrevCL = "";
        public string CLCustName = "";
        public string CustomerGroup = "";
        public string DiscReason = "";
        public string ProdDivision = "";
        public string IDControl = "";
        public string Value1 = "";
        public string Value2 = "";
        public string ApprovalMax = "";
        public string TotalCust = "0";
        public string TotalProd = "0";
        public string MaxDiscount = "0";
        public string FileQueue = "0";
        public string MaxFileSize = "0";
        public string ApprovalList = "";

        public Dictionary<string, string> ViewData = new Dictionary<string, string>();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack){
                if (Request.QueryString["cl"] != null &&
                    !(string.IsNullOrEmpty(Request.QueryString["cl"]))){

                    CLNumber = Request.QueryString["cl"];
                    //Test = CLNumber;
                    
                    ViewData = LoadRequest(CLNumber);
                    PrevCL = ViewData["PrevCL"];
                    CLCustName = ViewData["CLCustName"];
                    CustomerGroup = ViewData["CustomerGroup"];
                    DiscReason = ViewData["DiscReason"];
                    ProdDivision = ViewData["ProdDivision"];
                    IDControl = ViewData["IDControl"];
                    Value1 = ViewData["Value1"];
                    Value2 = ViewData["Value2"];
                    ApprovalMax = ViewData["ApprovalMax"];
                    MaxDiscount = GetMaxDiscount(CLNumber).ToString();
                    MaxFileSize = GetMaxFileSize(CLNumber).ToString();
                    FileQueue = GetFileQueued(CLNumber).ToString();
                    ApprovalList = GetCBApprovalList(IDControl, Value1, Value2);

                    DistributorList = getDistributor(ViewData["DistributorCode"].ToString());
                    ChannelList = getChannel(ViewData["Channel"].ToString());
                    TipeDiskon = getDiscType(ViewData["DiscType"].ToString());
                    InitDateStart = ViewData["DateStart"].ToString();
                    InitDateEnd = ViewData["DateEnd"].ToString();
                    
                    string btnRemove = "<td style=\"text-align:center;width:24px;\">" +
                        "<img class=\"btnRemove\" style=\"cursor:pointer;border:0;height:14px;" +
                        "width:14px;\" title=\"Remove\" alt=\"Remove\" src=\"images/delete.png\">" +
                        "</td>";

                    List<object> lstCustomers = GetRequestCustomer(CLNumber);
                    if (lstCustomers.Count > 0){
                        TotalCust = lstCustomers.Count.ToString();
                        for (int idxCustomer = 0; idxCustomer < lstCustomers.Count; idxCustomer++){
                            string tblClass = ((idxCustomer % 2) == 0) ? " class=\"odd\"" : " class=\"even\"";
                            string tblStyle = (idxCustomer == ((lstCustomers.Count) - 1)) ? " style=\"border-bottom: 1px solid rgb(204, 204, 204);\"" : "";
                            string TRStart = "<tr " + tblClass + tblStyle + ">";

                            Dictionary<string, object> dctCustomers = (Dictionary<string, object>)lstCustomers[idxCustomer];
                            string CustCode = "<td style=\"width:85px;\">" + dctCustomers["CustCode"].ToString() + "</td>";
                            string CustCodeAZI = "<td style=\"width:85px;\">" + dctCustomers["CSCodeAZI"].ToString() + "</td>";
                            string CustName = "<td style=\"width:267px;\">" + dctCustomers["CustName"].ToString() + "</td>";
                            string CustAddr = "<td style=\"width:258px;\">" + dctCustomers["CustAddress"].ToString() + "</td>";

                            RequestCustomers += TRStart + CustCode + CustCodeAZI + CustName +
                                CustAddr + btnRemove + "</tr>";
                        }
                    }else{
                        RequestCustomers = "<td colspan=\"5\" style=\"padding:15px 0; " +
                            "text-align:center; width:753px; height:147px; " +
                            "border-bottom:1px solid #ccc;\">Please select Customer(s)</td>";
                    }

                    List<object> lstProducts = GetRequestProduct(CLNumber);
                    if (lstProducts.Count > 0){
                        TotalProd = lstProducts.Count.ToString();
                        for (int idxProduct = 0; idxProduct < lstProducts.Count; idxProduct++){
                            string tblClass = ((idxProduct % 2) == 0) ? " class=\"odd\"" : " class=\"even\"";
                            string tblStyle = (idxProduct == ((lstProducts.Count) - 1)) ? " style=\"border-bottom: 1px solid rgb(204, 204, 204);\"" : "";
                            string TRStart = "<tr " + tblClass + tblStyle + ">";

                            Dictionary<string, object> dctProducts = (Dictionary<string, object>)lstProducts[idxProduct];
                            string ProductCode = "<td style=\"width:85px;\">" + dctProducts["ProductCode"].ToString() + "</td>";
                            string ProductName = "<td style=\"width:458px;\">" + dctProducts["ProductName"].ToString() + "</td>";

                            double formatDisc = Convert.ToDouble(dctProducts["Discount"]);
                            string ProductDisc = "<td style=\"width:76px;\">" +
                                "<input class=\"txtDiskon\" type=\"text\" style=\"font-size:12px;width:72px;padding:0;margin:0;" +
                                "text-align:right;\" value=\"" + formatDisc.ToString() + "\" name=\"txtDiskon[]\"></td>";
                            string ProductQty = "<td style=\"width:76px;\">" +
                                "<input class=\"txtQty\" type=\"text\" style=\"font-size:12px;width:72px;padding:0;margin:0;" +
                                "text-align:right;\" value=\"" + dctProducts["Quantity"].ToString() + "\" name=\"txtJuml[]\"></td>";

                            RequestProducts += TRStart + ProductCode + ProductName +
                                ProductDisc + ProductQty + btnRemove + "</tr>";
                        }
                    }else{
                        RequestProducts = "<td colspan=\"5\" style=\"padding:15px 0; " +
                            "text-align:center; width:753px; height:147px; " +
                            "border-bottom:1px solid #ccc;\">Please select product(s)</td>";
                    }

                    List<object> lstFiles = GetRequestFiles(CLNumber);
                    if (lstFiles.Count > 0){
                        string ULStart = "<ul id=\"olDoc\">";
                        string ULEnd = "</ul>";
                        string btnDelFile = "<div class=\"btnDelFile btnForm fl_right\" " +
                            "style=\"padding:0;margin:0;font-size:11px;line-height:12px;" +
                            "height:15px;width:24px;\" title=\"Remove\">x</div><div class=\"cl_right\"></div>";

                        RequestFiles = ULStart;
                        for (int idxFiles = 0; idxFiles < lstFiles.Count; idxFiles++){
                            Dictionary<string, object> dctFiles = (Dictionary<string, object>)lstFiles[idxFiles];
                            string IDFile = "<input type=\"hidden\" value=\"" +
                                dctFiles["IDSeq"].ToString() + "\" name=\"hidFList[]\" class=\"hidFList\" />";
                            string FileNames = "<li><a href=\"" + dctFiles["FileLocation"].ToString() +
                                dctFiles["FileName"].ToString() + "\">" + dctFiles["FileName"].ToString() + "</a>" +
                                btnDelFile + "</li>";

                            RequestFiles += IDFile + FileNames;
                        }
                        RequestFiles += ULEnd;
                    }
                }else{
                    Session["ErrCode"] = "ES-404";
                    Response.Redirect("ErrorPage.aspx", true);
                }
            }
        }

        private static Dictionary<string, string> LoadRequest(string CLNum){
            Dictionary<string, string> result = new Dictionary<string, string>();

            string qry = "SELECT tbl_Request.PrevCL, tbl_Request.DistributorCode, " +
            "tbl_Request.CLCustName, tbl_Request.CustomerGroup, tbl_Request.Channel, " + 
            "CONVERT(varchar,tbl_Request.DateRequest,105) AS DateRequest, " + 
            "CONVERT(varchar,tbl_Request.DateStart,105) AS DateStart, " + 
            "CONVERT(varchar,tbl_Request.DateEnd,105) AS DateEnd, " + 
            "tbl_Request.DiscType, tbl_Request.ProdDivision, " + 
            "tbl_Request.DiscReason, tbl_Request.IDControl, tbl_Request.Value1, " + 
            "tbl_Request.Value2, tbl_Request.ApprovalMax " + 
            "FROM Ebiz.dbo.tbl_Request " + 
            "WHERE (tbl_Request.CLNum = @CLNum) AND (tbl_Request.StatusCode = '203')";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn) {
                try {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNum);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows) {
                        while (rdr.Read()) {
                            string PrevCL = rdr.GetValue(0).ToString();
                            string DistributorCode = rdr.GetValue(1).ToString();
                            string CLCustName = rdr.GetValue(2).ToString();
                            string CustomerGroup = rdr.GetValue(3).ToString();
                            string Channel = rdr.GetValue(4).ToString();
                            string DateRequest = rdr.GetValue(5).ToString();
                            string DateStart = rdr.GetValue(6).ToString();
                            string DateEnd = rdr.GetValue(7).ToString();
                            string DiscType = rdr.GetValue(8).ToString();
                            string ProdDivision = rdr.GetValue(9).ToString();
                            string DiscReason = rdr.GetValue(10).ToString();
                            string IDControl = rdr.GetValue(11).ToString();
                            string Value1 = rdr.GetValue(12).ToString();
                            string Value2 = rdr.GetValue(13).ToString();
                            string ApprovalMax = rdr.GetValue(14).ToString();

                            result.Add("PrevCL", PrevCL);
                            result.Add("DistributorCode", DistributorCode);
                            result.Add("CLCustName", CLCustName);
                            result.Add("CustomerGroup", CustomerGroup);
                            result.Add("Channel", Channel);
                            result.Add("DateRequest", DateRequest);
                            result.Add("DateStart", DateStart);
                            result.Add("DateEnd", DateEnd);
                            result.Add("DiscType", DiscType);
                            result.Add("ProdDivision", ProdDivision);
                            result.Add("DiscReason", DiscReason);
                            result.Add("IDControl", IDControl);
                            result.Add("Value1", Value1);
                            result.Add("Value2", Value2);
                            result.Add("ApprovalMax", ApprovalMax);
                        }
                    }
                }catch(Exception ex){
                    string ErrMsg = ex.Message;
                }finally{
                    if(cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static double GetMaxDiscount(string CLNum) {
            double result = 0;
            string qry = "SELECT MAX(tbl_RequestProduct.Discount) AS MaxDisc " +
                "FROM Ebiz.dbo.tbl_RequestProduct " +
                "WHERE (CLNum = @CLNum)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNum);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            result = Convert.ToDouble(rdr.GetValue(0).ToString());
                        }
                    }
                }catch (Exception ex){
                    string ErrMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static double GetMaxFileSize(string CLNum) {
            double result = 0;
            string qry = "SELECT SUM(CONVERT(decimal,tbl_RequestFiles.FileSize,101)) AS TotalSize " +
                "FROM Ebiz.dbo.tbl_RequestFiles " +
                "WHERE (CLNum = @CLNum)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNum);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            result = Convert.ToDouble(rdr.GetValue(0).ToString());
                        }
                    }
                }catch (Exception ex){
                    string ErrMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static int GetFileQueued(string CLNum){
            int result = 0;
            string qry = "SELECT COUNT(tbl_RequestFiles.IDSeq) AS TotalQueue " +
                "FROM Ebiz.dbo.tbl_RequestFiles " +
                "WHERE (CLNum = @CLNum)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNum);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            result = Convert.ToInt32(rdr.GetValue(0).ToString());
                        }
                    }
                }catch (Exception ex){
                    string ErrMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string GetCBApprovalList(string IDControl, string Value1, string Value2) {
            string result = "";
            string qry = "SELECT UserAccounts.UserName, UserAccounts.FullName " +
                "FROM Ebiz.dbo.UserAccounts  " +
                "WHERE (UserAccounts.AppActor = 'Y') AND " +
                "(UserAccounts.StatusCode = '101') AND " +
                "((UserAccounts.IDRole IN (" +
	            "    (SELECT tbl_ApprovalStep.NextStep FROM Ebiz.dbo.tbl_ApprovalStep " +
	            "    WHERE (tbl_ApprovalStep.IDControl = 'DCLE') AND " +
	            "   (tbl_ApprovalStep.Value1 = '5') AND " +
	            "    (tbl_ApprovalStep.Value2 = '') AND " +
	            "    (tbl_ApprovalStep.StepSeq = 1) AND " +
	            "    (tbl_ApprovalStep.ActorCategory = 'ROLE')) " +
                ")) OR " +
                "(UserAccounts.UserName IN ( " +
	            "    (SELECT tbl_ApprovalStep.NextStep FROM Ebiz.dbo.tbl_ApprovalStep " +
	            "    WHERE (tbl_ApprovalStep.IDControl = 'DCLE') AND " +
	            "    (tbl_ApprovalStep.Value1 = '5') AND " +
	            "    (tbl_ApprovalStep.Value2 = '') AND " +
	            "    (tbl_ApprovalStep.StepSeq = 1) AND " +
	            "    (tbl_ApprovalStep.ActorCategory = 'UNAME')) " +
                "))) " +
                "ORDER BY UserAccounts.FullName ASC";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@IDControl", IDControl);
                    cmd.Parameters.AddWithValue("@Value1", Value1);
                    cmd.Parameters.AddWithValue("@Value2", Value2);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        result = "<div class='divAppList' style=''>" + 
                            "<select class='cbAppList' name='cbAppList[]'>" + 
                            "<option value='0'>--- Select ---</option>";
                        while (rdr.Read()){
                            string UserName = rdr.GetValue(0).ToString();
                            string FullName = rdr.GetValue(1).ToString();
                            string tmpOpt = "<option value='" + UserName + "'>" +
                                FullName + "</option>";

                            result += tmpOpt;

                        }
                        result += "</div>";
                    }
                }catch (Exception ex){
                    string ErrMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }

            return result;
        }

        /*
        private static int CheckRequest(string CLNumber, string SSID){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                string qry = "SELECT COUNT(CLNum) FROM Ebiz.dbo.tbl_Request WHERE " +
                    "(CLNum = @CLNum) AND (Token = @Token)";
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNumber);
                    cmd.Parameters.AddWithValue("@Token", SSID);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            result = Convert.ToInt32(rdr.GetValue(0));
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }
        */

        private static List<object> GetRequestCustomer(string CLNum) {
            List<object> result = new List<object>();
            string qry = "SELECT tbl_RequestCustomer.CLNum, " +
                "tbl_RequestCustomer.CustGroup, tbl_RequestCustomer.CustCode, " +
                "tbl_RequestCustomer.CSCodeAZI, tbl_RequestCustomer.CustName, " + 
                "tbl_RequestCustomer.CustAddress " +
                "FROM Ebiz.dbo.tbl_RequestCustomer WHERE " +
                "(tbl_RequestCustomer.CLNum = @CLNum)";

            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNum);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        while (rdr.Read()){
                            string CustGroup = rdr.GetValue(1).ToString();
                            string CustCode = rdr.GetValue(2).ToString();
                            string CSCodeAZI = rdr.GetValue(3).ToString();
                            string CustName = rdr.GetValue(4).ToString();
                            string CustAddress = rdr.GetValue(5).ToString();

                            Dictionary<string, object> dctResult = new Dictionary<string, object>();
                            dctResult.Add("CustGroup", CustGroup);
                            dctResult.Add("CustCode", CustCode);
                            dctResult.Add("CSCodeAZI", CSCodeAZI);
                            dctResult.Add("CustName", CustName);
                            dctResult.Add("CustAddress", CustAddress);

                            result.Add(dctResult);
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static List<object> GetRequestProduct(string CLNum) {
            List<object> result = new List<object>();

            string qry = "SELECT tbl_RequestProduct.CLNum, " +
                "tbl_RequestProduct.ProductCode, tbl_RequestProduct.ProductName, " + 
                "tbl_RequestProduct.Discount, tbl_RequestProduct.Quantity " +
                "FROM Ebiz.dbo.tbl_RequestProduct WHERE " +
                "tbl_RequestProduct.CLNum = @CLNum";

            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNum);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        while (rdr.Read()){
                            string ProductCode = rdr.GetValue(1).ToString();
                            string ProductName = rdr.GetValue(2).ToString();
                            string Discount = rdr.GetValue(3).ToString();
                            string Quantity = rdr.GetValue(4).ToString();

                            Dictionary<string, object> dctResult = new Dictionary<string, object>();
                            dctResult.Add("ProductCode", ProductCode);
                            dctResult.Add("ProductName", ProductName);
                            dctResult.Add("Discount", Discount);
                            dctResult.Add("Quantity", Quantity);

                            result.Add(dctResult);
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static List<object> GetRequestFiles(string CLNum){
            List<object> result = new List<object>();

            string qry = "SELECT tbl_RequestFiles.CLNum, tbl_RequestFiles.IDSeq, " +
                "tbl_RequestFiles.FileLocation, tbl_RequestFiles.FileName, " +
                "tbl_RequestFiles.FileType, tbl_RequestFiles.FileSize " +
                "FROM Ebiz.dbo.tbl_RequestFiles WHERE " +
                "(tbl_RequestFiles.CLNum = @CLNum) AND (StatusCode = '101')";

            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNum);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        double TotalFileSize = 0;
                        while (rdr.Read()){
                            string IDSeq = rdr.GetValue(1).ToString();
                            string FileLocation = rdr.GetValue(2).ToString();
                            string FileName = rdr.GetValue(3).ToString();
                            string FileType = rdr.GetValue(4).ToString();
                            string FileSize = rdr.GetValue(5).ToString();
                            
                            double FSize = Convert.ToDouble(FileSize);
                            TotalFileSize += FSize;

                            Dictionary<string, object> dctResult = new Dictionary<string, object>();
                            dctResult.Add("IDSeq", IDSeq);
                            dctResult.Add("FileLocation", FileLocation);
                            dctResult.Add("FileName", FileName);
                            dctResult.Add("FileType", FileType);
                            dctResult.Add("FileSize", FileSize);
                            dctResult.Add("TotalFileSize", TotalFileSize);

                            result.Add(dctResult);
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        protected static string getDistributor(string DistributorCode){
            string result = "<option value=\"0\">--- SELECT ---</option>";

            string strCon = DBUtilities.strConnection("DevCon");
            SqlConnection cn = new SqlConnection(strCon);
            using (cn){
                try {
                    cn.Open();

                    string qry = "SELECT DistributorCode, DistributorName FROM mstDistributor ORDER BY DistributorCode";
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        while (rdr.Read()){
                            string isSelected = (rdr.GetValue(0).ToString() == DistributorCode) ? " selected=\"selected\"" : "";
                            string strCode = "<option value=\"" + rdr.GetValue(0).ToString() + "\"" + isSelected + ">";
                            string strName = rdr.GetValue(1).ToString() + "</option>";

                            string opt = strCode + strName;
                            result += opt;
                        }
                    }
                }catch(Exception ex){
                    string ErrMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open) {
                        cn.Close();
                    }
                }
            }
            return result;
        }

        protected static string getChannel(string IDChannel){
            string result = "<option value=\"0\">--- SELECT ---</option>";
            string strCon = DBUtilities.strConnection("DevCon");
            SqlConnection sqlCon = new SqlConnection(strCon);
            using (sqlCon){
                sqlCon.Open();

                string strQuery = "SELECT IDChannel, ChannelName FROM mstChannel WHERE " +
                    "(StatusCode = '101') ORDER BY IDChannel";
                SqlCommand sqlCmd = new SqlCommand(strQuery, sqlCon);
                SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                if (sqlReader.HasRows){
                    while (sqlReader.Read()){
                        string isSelected = (sqlReader.GetValue(0).ToString() == IDChannel) ? " selected=\"selected\"" : "";
                        string strCode = "<option value=\"" + sqlReader.GetValue(0).ToString() + "\"" + isSelected + ">";
                        string strName = sqlReader.GetValue(1).ToString() + "</option>";
                        string opt = strCode + strName;

                        result += opt;
                    }
                }
                sqlCon.Close();
            }
            return result;
        }

        protected static string getDiscType(string DiscType) {
            string result = "";

            Dictionary<string, string> opt = new Dictionary<string, string>();
            opt.Add("DPF", "DPF");
            opt.Add("DPL", "DPL");
            foreach (KeyValuePair<string, string> kvp in opt) { 
                string isSelected = (kvp.Key == DiscType) ? " selected=\"selected\"" : "";
                string skey = "<option value=\"" + kvp.Key + "\"" + isSelected + ">";
                string sval = kvp.Value + "</option>\n";

                result += skey + sval;
            }

            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static Dictionary<string, object> GetRuleList(string RuleValue, string RuleValueExt)
        {
            Dictionary<string, object> result = new Dictionary<string, object>();

            string[] RulePrefix = new string[3];
            RulePrefix[0] = "CD";
            RulePrefix[1] = "CH";
            RulePrefix[2] = "DC";

            string[] RuleQuery = new string[3];
            RuleQuery[0] = "SELECT COUNT(IDControl) FROM tbl_ApprovalRule " +
                "WHERE (SUBSTRING(IDControl, 0, 3) = '" + RulePrefix[0].ToString() + "') AND " +
                "(Value1 = @Value1) AND (Value2 IS NOT NULL) AND (Value2 <> '')";
            RuleQuery[1] = "SELECT COUNT(IDControl) FROM tbl_ApprovalRule " +
                "WHERE (SUBSTRING(IDControl, 0, 3) = '" + RulePrefix[1].ToString() + "') AND " +
                "(Value1 = @Value1)";
            RuleQuery[2] = "SELECT COUNT(IDControl) FROM tbl_ApprovalRule " +
                "WHERE (SUBSTRING(IDControl, 0, 3) = '" + RulePrefix[2].ToString() + "')";

            int _ruleCount = 0;
            int RulePrefixLen = RulePrefix.Length;
            for (int idx = 0; idx < RulePrefixLen; idx++){
                switch (idx){
                    case 0:
                        double zroCD = 0;
                        if (Double.TryParse(RuleValueExt, out zroCD)){
                            if (double.Parse(RuleValueExt) > 0){
                                Dictionary<string, object> lstParamsCD = new Dictionary<string, object>();
                                lstParamsCD.Add("@Value1", RuleValue);
                                _ruleCount = RuleCount(RuleQuery[idx], lstParamsCD);
                            }
                        }
                        break;
                    case 1:
                        Dictionary<string, object> lstParamsCH = new Dictionary<string, object>();
                        lstParamsCH.Add("@Value1", RuleValue);
                        _ruleCount = RuleCount(RuleQuery[idx], lstParamsCH);
                        break;
                    case 2:
                        double zroDC = 0;
                        if (Double.TryParse(RuleValueExt, out zroDC)){
                            if (double.Parse(RuleValueExt) > 0){
                                _ruleCount = RuleCount(RuleQuery[idx]);
                            }
                        }
                        break;
                }

                if (_ruleCount > 0){
                    string _MaxSteps = "0";
                    switch (idx){
                        case 0:
                            Dictionary<string, object> tmpResultCD = new Dictionary<string, object>();
                            tmpResultCD = CDCList(RulePrefix[0], RuleValue, RuleValueExt);
                            int _tmpMaxStepsCD = 0;
                            if (tmpResultCD["RuleExist"].ToString() == "1"){
                                _tmpMaxStepsCD = MaxSteps("CD", tmpResultCD["RuleCtrl"].ToString(), tmpResultCD["OptValue"].ToString(), tmpResultCD["OptValueExt"].ToString());
                                if (_tmpMaxStepsCD > 0){
                                    result = tmpResultCD;

                                    _MaxSteps = _tmpMaxStepsCD.ToString();
                                    result.Add("MaxSteps", _MaxSteps);
                                    result.Add("AppList", GetApprovalList(tmpResultCD["RuleCtrl"].ToString(), tmpResultCD["OptValue"].ToString(), tmpResultCD["OptValueExt"].ToString(), 1, ""));
                                }else{
                                    result["RuleExist"] = "0";
                                }
                            }else{
                                result["RuleExist"] = "0";
                            }
                            break;
                        case 1:
                            int _tmpMaxStepsCH = MaxSteps("CH", "CHAN", RuleValue, string.Empty);
                            if (_tmpMaxStepsCH > 0){
                                result.Add("RuleExist", "1");
                                result.Add("FixDiscount", "0");
                                result.Add("RuleCtrl", "CHAN");
                                result.Add("OptSymbol", "=");
                                result.Add("OptValue", RuleValue);
                                result.Add("OptValueReal", RuleValue);
                                result.Add("OptValueExt", string.Empty);

                                _MaxSteps = _tmpMaxStepsCH.ToString();
                                result.Add("MaxSteps", _MaxSteps);
                                result.Add("AppList", GetApprovalList("CHAN", RuleValue, string.Empty, 1, string.Empty));
                            }else{
                                result.Add("RuleExist", "0");
                            }
                            break;
                        case 2:
                            Dictionary<string, object> tmpResultDC = new Dictionary<string, object>();
                            tmpResultDC = CDCList(RulePrefix[2], RuleValueExt, string.Empty);

                            int _tmpMaxStepsDC = 0;
                            if (tmpResultDC["RuleExist"].ToString() == "1"){
                                _tmpMaxStepsDC = MaxSteps("DC", tmpResultDC["RuleCtrl"].ToString(), tmpResultDC["OptValue"].ToString(), tmpResultDC["OptValueExt"].ToString());
                                if (_tmpMaxStepsDC > 0){
                                    result = tmpResultDC;

                                    _MaxSteps = _tmpMaxStepsDC.ToString();
                                    result.Add("MaxSteps", _MaxSteps);
                                    result.Add("AppList", GetApprovalList(tmpResultDC["RuleCtrl"].ToString(), tmpResultDC["OptValue"].ToString(), string.Empty, 1, string.Empty));
                                }else{
                                    result["RuleExist"] = "0";
                                }
                            }else{
                                result["RuleExist"] = "0";
                            }
                            break;
                    }
                    break;
                }else{
                    if (idx == (RulePrefixLen - 1)){
                        result.Add("RuleExist", "0");
                    }
                }
            }
            return result;
        }

        private static int RuleCount(string QueryString){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();
                SqlCommand cmd = new SqlCommand(QueryString, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    if (rdr.Read()){
                        string countRow = rdr.GetValue(0).ToString();
                        result = Convert.ToInt32(countRow);
                    }
                }
                cn.Close();
            }
            return result;
        }

        private static int RuleCount(string QueryString, Dictionary<string, object> Params)
        {
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();
                SqlCommand cmd = new SqlCommand(QueryString, cn);
                foreach (KeyValuePair<string, object> dict in Params){
                    cmd.Parameters.AddWithValue(dict.Key, dict.Value);
                }
                
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    if (rdr.Read()){
                        string countRow = rdr.GetValue(0).ToString();
                        result = Convert.ToInt32(countRow);
                    }
                }
                cn.Close();
            }
            return result;
        }

        private static int MaxSteps(string RulePrefix, string RuleControl, string RuleValue,
            string RuleValueExt){

            int result = 0;
            string ruleVal = "";
            string ruleValExt = "";
            switch (RulePrefix){
                case "CH":
                    ruleVal = "'" + RuleValue + "'";
                    ruleValExt = "''";
                    break;
                case "CD":
                    ruleVal = "'" + RuleValue + "'";
                    ruleValExt = "'" + RuleValueExt + "'";
                    break;
                case "DC":
                    ruleVal = "'" + RuleValue + "'";
                    ruleValExt = "''";
                    break;
            }

            string qry = "SELECT ISNULL(MAX(StepSeq), 0) AS MaxSteps FROM Ebiz.dbo.tbl_ApprovalStep WHERE " +
                "(IDControl = '" + RuleControl + "') AND (Value1 = " + ruleVal + ") AND " +
                "(Value2 = " + ruleValExt + ") AND (NextStep <> '0')";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();
                SqlCommand cmd = new SqlCommand(qry, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    if (rdr.Read()){
                        string rval = rdr.GetValue(0).ToString();
                        result = Convert.ToInt32(rval);
                    }
                }else{
                    result = 0;
                }
            }
            return result;
        }

        private static Dictionary<string, object> CDCList(string RulePrefix, string RuleValue,
            string RuleValueExt){

            Dictionary<string, object> result = new Dictionary<string, object>();
            string qry = "";
            switch (RulePrefix){
                case "DC":
                    qry = "SELECT IDControl,  Operator1, Value1, FixDisc, Value1, Value2 FROM tbl_ApprovalRule " +
                        "WHERE (SUBSTRING(IDControl, 0, 3) = '" + RulePrefix + "')";
                    break;
                case "CD":
                    qry = "SELECT IDControl, Operator2, Value2, FixDisc, Value1, Value2 FROM tbl_ApprovalRule " +
                        "WHERE (SUBSTRING(IDControl, 0, 3) = '" + RulePrefix + "') AND (Value1 = '" + RuleValue + "')";
                    break;
            }

            double testValue = (RulePrefix == "DC") ? double.Parse(RuleValue) : double.Parse(RuleValueExt);
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();

                SqlCommand cmd = new SqlCommand(qry, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    while (rdr.Read()){
                        string IDCtrl = rdr.GetValue(0).ToString();
                        string OptSym = rdr.GetValue(1).ToString();
                        string OptVal = rdr.GetValue(2).ToString();
                        string FixDisc = rdr.GetValue(3).ToString();
                        string CtrlValue = rdr.GetValue(4).ToString();
                        string OptValExt = rdr.GetValue(5).ToString();

                        bool isExist = isInRange(OptSym, testValue, double.Parse(OptVal));
                        if (isExist == true){
                            result.Add("RuleExist", "1");
                            result.Add("FixDiscount", FixDisc);
                            result.Add("RuleCtrl", IDCtrl);
                            result.Add("OptSymbol", OptSym);
                            string _optval = (RulePrefix == "DC") ? OptVal : CtrlValue;
                            result.Add("OptValue", _optval);
                            result.Add("OptValueReal", RuleValue);
                            result.Add("OptValueExt", OptValExt);

                            break;
                        }
                    }
                }else{
                    result.Add("RuleExist", "0");
                }
                cn.Close();

                /*int resultLen = result.Count;
                if (resultLen <= 0){
                    result.Add("RuleExist", "0");
                }*/
            }
            return result;
        }

        private static bool isInRange(string OptSymbol, double testRuleValue, double vsRuleValue){
            bool result = false;
            switch (OptSymbol){
                case "=":
                    result = (testRuleValue == vsRuleValue);
                    break;
                case "<":
                    result = (testRuleValue < vsRuleValue);
                    break;
                case ">":
                    result = (testRuleValue > vsRuleValue);
                    break;
                case "<=":
                    result = (testRuleValue <= vsRuleValue);
                    break;
                case ">=":
                    result = (testRuleValue >= vsRuleValue);
                    break;
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<object> GetApprovalList(string RuleControl, string RuleValue, string RuleValueExt,
            int ApprovalLayer, string CurrentApproval){

            List<object> result = new List<Object>();
            string xs = "";
            Dictionary<string, string> dctAppStep = new Dictionary<string, string>();
            if (string.IsNullOrEmpty(CurrentApproval)){
                dctAppStep = AppStepList(RuleControl, RuleValue, RuleValueExt, ApprovalLayer);
            }else{
                dctAppStep = AppStepList(RuleControl, RuleValue, RuleValueExt, ApprovalLayer, CurrentApproval);
            }

            string sRole = "";
            string sUname = "";
            foreach (KeyValuePair<string, string> oAppList in dctAppStep){
                if (oAppList.Key == "ROLE"){
                    sRole = oAppList.Value;
                }else if (oAppList.Key == "UNAME"){
                    sUname = oAppList.Value;
                }else{
                    xs = oAppList.Value;
                }
            }

            string xRole = (string.IsNullOrEmpty(sRole)) ? "'0'" : sRole;
            string xUname = (string.IsNullOrEmpty(sUname)) ? "''" : sUname;

            string qry = "SELECT UserName, FullName " +
                "FROM Ebiz.dbo.UserAccounts WHERE (AppActor = 'Y') AND (StatusCode = '101') AND " +
                "((IDRole IN (" + xRole + ")) OR " +
                "(UserName IN (" + xUname + "))) " +
                "ORDER BY FullName";

            result = AppUserList(qry);
            return result;
        }

        private static Dictionary<string, string> AppStepList(string RuleControl, string RuleValue,
            string RuleValueExt, int ApprovalLayer){

            Dictionary<string, string> result = new Dictionary<string, string>();
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();

                string qry = "SELECT NextStep FROM Ebiz.dbo.tbl_ApprovalStep " +
                    "WHERE (IDControl = '" + RuleControl + "') AND (Value1 = '" + RuleValue + "') " +
                    "AND (Value2 = '" + RuleValueExt + "') " + "AND (StepSeq = " + ApprovalLayer + ") " +
                    "GROUP BY NextStep";
                SqlCommand cmd = new SqlCommand(qry, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    int zro = 0;
                    List<string> lstResultInt = new List<string>();
                    List<string> lstResultStr = new List<string>();
                    while (rdr.Read()){
                        string tmpResult = rdr.GetValue(0).ToString();

                        if (int.TryParse(tmpResult, out zro)){
                            lstResultInt.Add(tmpResult);
                        }else{
                            lstResultStr.Add("'" + tmpResult + "'");
                        }
                    }
                    string sRole = "0";
                    string sUname = "''";

                    if ((lstResultInt.Count) > 0){
                        string[] strRole = lstResultInt.ToArray();
                        sRole = string.Join(",", strRole);
                    }

                    if ((lstResultStr.Count) > 0){
                        string[] strUname = lstResultStr.ToArray();
                        sUname = string.Join(",", strUname);
                    }
                    result.Add("ROLE", sRole);
                    result.Add("UNAME", sUname);
                }
                cn.Close();
            }
            return result;
        }

        private static Dictionary<string, string> AppStepList(string RuleControl, string RuleValue, string RuleValueExt,
            int ApprovalLayer, string CurrentApproval){

            Dictionary<string, string> result = new Dictionary<string, string>();
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();
                string xtra = " AND ((CurrentStep = '" + CurrentApproval + "') OR ";
                xtra += "(CurrentStep = CONVERT(NVARCHAR,(SELECT IDRole FROM Ebiz.dbo.UserAccounts WHERE UserName = '" + CurrentApproval + "')))) ";
                string qry = "SELECT NextStep FROM Ebiz.dbo.tbl_ApprovalStep " +
                    "WHERE (IDControl = '" + RuleControl + "') AND (Value1 = '" + RuleValue + "') " +
                    "AND (Value2 = '" + RuleValueExt + "') " + "AND (StepSeq = " + ApprovalLayer + ") " + xtra +
                    "GROUP BY NextStep";

                SqlCommand cmd = new SqlCommand(qry, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    int zro = 0;
                    List<string> lstResultInt = new List<string>();
                    List<string> lstResultStr = new List<string>();
                    while (rdr.Read()){
                        string tmpResult = rdr.GetValue(0).ToString();

                        if (int.TryParse(tmpResult, out zro)){
                            lstResultInt.Add(tmpResult);
                        }else{
                            lstResultStr.Add("'" + tmpResult + "'");
                        }
                    }
                    string sRole = "0";
                    string sUname = "''";

                    if ((lstResultInt.Count) > 0){
                        string[] strRole = lstResultInt.ToArray();
                        sRole = string.Join(",", strRole);
                    }

                    if ((lstResultStr.Count) > 0){
                        string[] strUname = lstResultStr.ToArray();
                        sUname = string.Join(",", strUname);
                    }

                    result.Add("ROLE", sRole);
                    result.Add("UNAME", sUname);
                }
                cn.Close();
            }
            return result;
        }

        private static List<object> AppUserList(string Query){
            List<object> result = new List<object>();
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();

                SqlCommand cmd = new SqlCommand(Query, cn);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    while (rdr.Read()){
                        string dctKey = rdr.GetValue(0).ToString();
                        string dctVal = rdr.GetValue(1).ToString();

                        Dictionary<string, object> dctResult = new Dictionary<string, object>();
                        dctResult.Add("appKey", dctKey);
                        dctResult.Add("appVal", dctVal);

                        result.Add(dctResult);
                    }
                }
                cn.Close();
            }
            return result;
        }

        private static string GetEmail(string PersonID){
            string result = "";

            string qry = "SELECT Email FROM Ebiz.dbo.UserAccounts WHERE (UserName = @UserName)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@UserName", PersonID);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            result = rdr.GetValue(0).ToString();
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string GetFullName(string PersonID)
        {
            string result = "";

            string qry = "SELECT FullName FROM Ebiz.dbo.UserAccounts WHERE (UserName = @UserName)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@UserName", PersonID);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            result = rdr.GetValue(0).ToString();
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static Dictionary<string, object> SendEmail(string MailTo, string CLNumber, string PageLink,
            EmailMode SendMode, EmailEngine EmailCodeBy, bool isCC){

            Dictionary<string, object> result = new Dictionary<string, object>();

            //string EmailAddress = GetEmail(MailTo);
            string EmailAddress = "";
            switch (isCC){
                case true: EmailAddress = GetEmail(Func.GetUserInfo(Func.ShowInfo._UserID));
                    break;
                case false: EmailAddress = GetEmail(MailTo);
                    break;
            }
            string EmailSubject = "BizCase request with CL Number " + CLNumber;
            if (!(string.IsNullOrEmpty(EmailAddress))){
                switch (EmailCodeBy){
                    case EmailEngine.CDOSYS:
                        //code for sending email via CDOSYS Mode
                        break;
                    case EmailEngine.NETMAIL:
                        Dictionary<string, object> dctConfig = GetEmailConfig();

                        MailMessage Mailer = new MailMessage();
                        Mailer.Subject = EmailSubject;
                        Mailer.From = new MailAddress(dctConfig["FromEmail"].ToString(), dctConfig["FromName"].ToString());
                        Mailer.Priority = (MailPriority)Convert.ToInt32(dctConfig["Priority"]);
                        Mailer.IsBodyHtml = (bool)Convert.ToBoolean(dctConfig["IsBodyHTML"]);

                        Mailer.To.Add(EmailAddress);

                        Dictionary<string, string> dctEmail = new Dictionary<string, string>();
                        dctEmail["Subject"] = EmailSubject;
                        dctEmail["Requester"] = Func.GetUserInfo(Func.ShowInfo._FullName);
                        dctEmail["AppvPerson"] = GetFullName(MailTo);
                        dctEmail["AppvPersonExt"] = "";
                        dctEmail["CLNum"] = CLNumber;
                        dctEmail["Link"] = PageLink;

                        Mailer.Body = CLSMail.GenerateMessage(dctEmail, CLSMail.MessageMode.REQUEST);

                        SmtpClient _SMTP = new SmtpClient();
                        _SMTP.DeliveryMethod = (SmtpDeliveryMethod)Convert.ToInt32(dctConfig["DeliveryMethod"]);
                        _SMTP.UseDefaultCredentials = (bool)Convert.ToBoolean(dctConfig["DefaultCredentials"]);
                        if (!(dctConfig["UserName"] == null && dctConfig["Password"] == null)){
                            _SMTP.Credentials = new NetworkCredential(dctConfig["UserName"].ToString(), dctConfig["Password"].ToString());
                        }_SMTP.Host = (string)dctConfig["Host"].ToString();
                        _SMTP.Port = (int)Convert.ToInt32(dctConfig["Port"]);
                        _SMTP.EnableSsl = (bool)Convert.ToBoolean(dctConfig["EnableSSL"]);
                        _SMTP.Timeout = (int)Convert.ToInt32(dctConfig["Timeout"]);

                        try{
                            _SMTP.Send(Mailer);
                            result["Status"] = 1;
                            result["Message"] = "Email Sent!";
                        }catch (Exception ex){
                            string ErrMsg = ex.Message;
                            result["Status"] = 0;
                            result["Message"] = ErrMsg;
                        }
                        break;
                }
            }else{
                result["Status"] = 0;
                result["Message"] = "Email address is empty!";
            }
            return result;
        }

        private static Dictionary<string, object> GetEmailConfig(){
            Dictionary<string, object> result = new Dictionary<string, object>();
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                string qry = "SELECT ConfigName, ConfigValue, Description FROM mstConfig WHERE " +
                    "ConfigGroup = 'Email'";
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        while (rdr.Read()){
                            string CFGName = rdr.GetValue(0).ToString();
                            string CFGValue = rdr.GetValue(1).ToString();
                            string CFGDesc = rdr.GetValue(2).ToString();

                            result.Add(CFGName, CFGValue);
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static int ExecQuery(string query){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    result = cmd.ExecuteNonQuery();
                }catch (Exception e){
                    string errMsg = e.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static int ExecQuery(string query, Dictionary<string, object> Params){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    foreach (KeyValuePair<string, object> kvp in Params){
                        cmd.Parameters.AddWithValue(kvp.Key, kvp.Value);
                    }
                    result = cmd.ExecuteNonQuery();
                }catch (Exception e){
                    string errMsg = e.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }
        
        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static int RollBackUpdate(string ID)
        {

            int result = 0;
            string qryWhere = "WHERE (CLNum = '" + ID + "')";

            List<string> qryCheck = new List<string>();
            qryCheck.Add("SELECT COUNT(CLNum) FROM Ebiz.dbo.tbl_RequestFiles " + qryWhere);
            qryCheck.Add("SELECT COUNT(CLNum) FROM Ebiz.dbo.tbl_RequestApproval " + qryWhere);
            qryCheck.Add("SELECT COUNT(CLNum) FROM Ebiz.dbo.tbl_RequestProduct " + qryWhere);
            qryCheck.Add("SELECT COUNT(CLNum) FROM Ebiz.dbo.tbl_RequestCustomer " + qryWhere);
            qryCheck.Add("SELECT COUNT(CLNum) FROM Ebiz.dbo.tbl_Request " + qryWhere);

            List<string> qryRollBack = new List<string>();
            qryRollBack.Add("DELETE FROM Ebiz.dbo.tbl_RequestFiles " + qryWhere);
            qryRollBack.Add("DELETE FROM Ebiz.dbo.tbl_RequestApproval " + qryWhere);
            qryRollBack.Add("DELETE FROM Ebiz.dbo.tbl_RequestProduct " + qryWhere);
            qryRollBack.Add("DELETE FROM Ebiz.dbo.tbl_RequestCustomer " + qryWhere);
            qryRollBack.Add("DELETE FROM Ebiz.dbo.tbl_Request " + qryWhere);

            int CountQry = qryCheck.Count;
            for (var idx = 0; idx < CountQry; idx++)
            {
                int tmpResult = ExecQuery(qryRollBack[idx].ToString());
                //if(DataCount(qryCheck[idx].ToString()) > 0){
                //    int tmpResult = ExecQuery(qryRollBack[idx].ToString());
                //}
            }
            result = 0;
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static Dictionary<string, object> ShowDataList(string ID, string Name,
            int StartIndex, ShowMode showMode)
        {

            int SearchMode = 0;
            Dictionary<string, object> result = new Dictionary<string, object>();
            if (!(Name == string.Empty))
            {
                int TotalRows = 0;
                string TableName = "";
                string FieldName = "";
                string ProcedureName = "";
                switch (showMode)
                {
                    case ShowMode._Customer:
                        TableName = "v_mapCustomer";
                        FieldName = "CUSTNAME";
                        ProcedureName = "CustomerList";
                        SearchMode = 0;
                        break;
                    case ShowMode._Product:
                        TableName = "v_mapProduct";
                        FieldName = "ProductName";
                        ProcedureName = "ProductList";
                        SearchMode = 1;
                        break;
                }

                string strQry = "SELECT COUNT(DistributorCode) AS TotalRows FROM " + TableName +
                    " WHERE (DistributorCode = '" + ID + "') AND (" + FieldName + " LIKE '%" +
                    Name + "%')";
                SqlConnection sqlCon = new SqlConnection(DBUtilities.strConnection("devCon"));
                using (sqlCon)
                {
                    sqlCon.Open();

                    SqlCommand sqlCmd = new SqlCommand(strQry, sqlCon);
                    SqlDataReader sqlRdr = sqlCmd.ExecuteReader();
                    if (sqlRdr.HasRows)
                    {
                        if (sqlRdr.Read())
                        {
                            TotalRows = Convert.ToInt32(sqlRdr.GetValue(0).ToString());
                        }
                    }
                    sqlCon.Close();
                }

                if (TotalRows > 0)
                {
                    List<string> SearchKey = new List<string>();
                    SearchKey.Add(ID);
                    SearchKey.Add(Name);

                    result = GetDataList(StartIndex, TotalRows, ProcedureName, SearchKey, SearchMode);
                }
            }
            return result;
        }

        private static Dictionary<string, object> GetDataList(int StartIndex, int TotalRows,
            string ProcedurName, List<string> SearchKey, int SearchMode)
        {

            Dictionary<string, object> result = new Dictionary<string, object>();

            int ShowRows = 15;
            int NumRows = TotalRows;
            int TPages = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(NumRows / ShowRows)));

            SqlConnection sqlCon = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (sqlCon)
            {
                List<object> TblRows = new List<object>();
                int StartOffset = (StartIndex < 2) ? 0 : ((StartIndex * ShowRows) - ShowRows) + 1;
                int EndOffset = (StartIndex > 1) ? (ShowRows - 1) : ShowRows;

                if (NumRows > 0)
                {
                    sqlCon.Open();
                    SqlCommand sqlCmd = new SqlCommand(ProcedurName, sqlCon);
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.Parameters.Add(new SqlParameter("Start", StartOffset));
                    sqlCmd.Parameters.Add(new SqlParameter("ShowRows", EndOffset));
                    sqlCmd.Parameters.Add(new SqlParameter("ID", SearchKey[0]));
                    sqlCmd.Parameters.Add(new SqlParameter("Name", SearchKey[1]));

                    SqlDataReader sqlRdr = sqlCmd.ExecuteReader();
                    if (sqlRdr.HasRows)
                    {
                        int idx = 0;
                        while (sqlRdr.Read())
                        {
                            string tmpResult1 = sqlRdr.GetValue(1).ToString();
                            string tmpResult2 = sqlRdr.GetValue(2).ToString();
                            string tmpResult3 = sqlRdr.GetValue(3).ToString();
                            string tmpResult4 = "";
                            if (SearchMode == 0)
                            {
                                tmpResult4 = sqlRdr.GetValue(4).ToString();
                            }

                            Dictionary<string, object> tblRow = new Dictionary<string, object>();
                            if (SearchMode == 0)
                            {
                                tblRow.Add("ID", tmpResult1);
                                tblRow.Add("IDAzi", tmpResult2);
                                tblRow.Add("Name", tmpResult3);
                                tblRow.Add("Address", tmpResult4);
                            }
                            else if (SearchMode == 1)
                            {
                                tblRow.Add("ID", tmpResult1);
                                tblRow.Add("Name", tmpResult2);
                            }
                            tblRow.Add("Distributor", tmpResult3);
                            TblRows.Add(tblRow);

                            idx++;
                        }
                    }
                    sqlCon.Close();

                    int ActivePage = (StartIndex == 0) ? 1 : StartIndex;
                    int PrevPage = (ActivePage > 1) ? (ActivePage - 1) : 0;
                    int NextPage = (ActivePage < TPages) ? (ActivePage + 1) : 0;
                    int TotalPages = (TPages > 1) ? TPages : 1;

                    result.Add("ShowRows", ShowRows);
                    result.Add("PrevPage", PrevPage);
                    result.Add("ActivePage", ActivePage);
                    result.Add("NextPage", NextPage);
                    result.Add("TotalPages", TotalPages);
                    result.Add("Content", TblRows);
                }
            }
            return result;
        }

        private static List<object> DeletePrevData(string CLNum, string TableName) {
            List<object> result = new List<object>();
            string qry = "DELETE FROM Ebiz.dbo." + TableName + " WHERE " +
                "(" + TableName + ".CLNum = @CLNum)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn) {
                try {
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNum);
                    if (cmd.ExecuteNonQuery() > 0) {
                        result.Add(1);
                        result.Add("Delete Success");
                    }
                }catch(Exception ex){
                    string ErrMsg = ex.Message;
                    result.Add(0);
                    result.Add(ErrMsg);
                }finally{
                    if (cn.State == ConnectionState.Open) {
                        cn.Close();
                    }
                }
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<object> SaveNewRequest(string IDCL, string Distributor, string DiscountType, string Channel,
            string CLCustName, string CustGroup, string Reason, string DateStart, string DateEnd,
            string PrevCL, string RuleCtrl, string RuleVal, string RuleValExt, string CurrentStep,
            string NextStep, int ApprovalStep, int ApprovalMax, string ProductDivision)
        {
            List<object> result = new List<object>();

            List<object> tmp = DeletePrevData(IDCL, "tbl_Request");
            if ((int) tmp[0] == 1) { 
                SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
                using (cn){
                    cn.Open();

                    string qry = "INSERT INTO tbl_Request (CLNum, PrevCL, DistributorCode, CLCustName, " +
                        "CustomerGroup, Channel, DateRequest, DateStart, DateEnd, DiscType, DiscReason, " +
                        "Token, TokenExpiry, IDControl, Value1, Value2, ApprovalCurrent, ApprovalNext, " +
                        "ApprovalStep, ApprovalMax, CreatedBy, CreatedDate, ProdDivision, StatusCode) VALUES " +
                        "(@CLNum, @PrevCL, @DistributorCode, @CLCustName, " +
                        "@CustomerGroup, @Channel, GetDate(), @DateStart, @DateEnd, @DiscType, @DiscReason, " +
                        "@Token, @TokenExpiry, @IDControl, @Value1, @Value2, @ApprovalCurrent, @ApprovalNext, " +
                        "@ApprovalStep, @ApprovalMax, @CreatedBy, GetDate(), @ProdDivision, @StatusCode)";

                    //string DateRequest = DateTimeOffset.Now.Date.ToString("yyyy/MM/dd HH:mm:ss");
                    string _DateStart = Func.FormatDate(DateStart, Func.DTFormat.DMYtoYMD, "-", "/");
                    string _DateEnd = Func.FormatDate(DateEnd, Func.DTFormat.DMYtoYMD, "-", "/");
                    string CreatedBy = Func.GetUserInfo(Func.ShowInfo._UserID);
                    string Token = Func.GetUserInfo(Func.ShowInfo._SSID);

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", IDCL);
                    cmd.Parameters.AddWithValue("@PrevCL", PrevCL);
                    cmd.Parameters.AddWithValue("@DistributorCode", Distributor);
                    cmd.Parameters.AddWithValue("@CLCustName", CLCustName);
                    cmd.Parameters.AddWithValue("@CustomerGroup", CustGroup);
                    cmd.Parameters.AddWithValue("@Channel", Channel);
                    //cmd.Parameters.AddWithValue("@DateRequest", DateRequest);
                    cmd.Parameters.AddWithValue("@DateStart", _DateStart);
                    cmd.Parameters.AddWithValue("@DateEnd", _DateEnd);
                    cmd.Parameters.AddWithValue("@DiscType", DiscountType);
                    cmd.Parameters.AddWithValue("@DiscReason", Reason);
                    cmd.Parameters.AddWithValue("@Token", Token);

                    cmd.Parameters.AddWithValue("@TokenExpiry", DateTime.Today.AddDays(7).ToString("yyyy/MM/dd hh:mm:ss"));
                    cmd.Parameters.AddWithValue("@IDControl", RuleCtrl);
                    cmd.Parameters.AddWithValue("@Value1", RuleVal);
                    cmd.Parameters.AddWithValue("@Value2", RuleValExt);
                    cmd.Parameters.AddWithValue("@ApprovalCurrent", CurrentStep);
                    cmd.Parameters.AddWithValue("@ApprovalNext", NextStep);
                    cmd.Parameters.AddWithValue("@ApprovalStep", ApprovalStep);
                    cmd.Parameters.AddWithValue("@ApprovalMax", ApprovalMax);
                    cmd.Parameters.AddWithValue("@CreatedBy", CreatedBy);
                    //cmd.Parameters.AddWithValue("@CreatedDate", DateRequest);
                    cmd.Parameters.AddWithValue("@ProdDivision", ProductDivision);
                    cmd.Parameters.AddWithValue("@StatusCode", "200");

                    try{
                        int qryExec = cmd.ExecuteNonQuery();
                        if (qryExec > 0){
                            result.Add(1);
                            result.Add(IDCL);
                            result.Add(Distributor);
                            result.Add(CustGroup);
                        }else{
                            result.Add(0);
                            result.Add("Update data failed!");
                        }
                    }catch (Exception ex){
                        string errMsg = ex.Message;
                        result.Add(0);
                        result.Add(errMsg);
                    }
                    cn.Close();
                }
            }else{
                result.Add(0);
                result.Add("Update data failed");
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static string SaveCustomers(string ID, string Distributor, string CSGroup,
            List<object> CSList){

            string result = "";
            
            List<object> tmp = DeletePrevData(ID, "tbl_RequestCustomer");
            if ((int)tmp[0] > 0){
                int csLen = CSList.Count;
                for (int idx = 0; idx < csLen; idx++){
                    Dictionary<string, object> dct = (Dictionary<string, object>)CSList[idx];
                    string CSID = dct["CSID"].ToString();
                    string CSIDAzi = dct["CSIDAzi"].ToString();
                    string CSName = dct["CSName"].ToString();
                    string CSAddress = dct["CSAddress"].ToString();

                    string qry = "INSERT INTO Ebiz.dbo.tbl_RequestCustomer (CLNum, DistributorCode, " +
                        "CustGroup, CustCode, CSCodeAZI, CustName, CustAddress) " +
                        "VALUES (@CLNum, @DistributorCode, " +
                        "@CustGroup, @CustCode, @CSCodeAZI, @CustName, @CustAddress)";
                    Dictionary<string, object> lstParams = new Dictionary<string, object>();
                    lstParams.Add("@CLNum", ID);
                    lstParams.Add("@DistributorCode", Distributor);
                    lstParams.Add("@CustGroup", CSGroup);
                    lstParams.Add("@CustCode", CSID);
                    lstParams.Add("@CSCodeAZI", CSIDAzi);
                    lstParams.Add("@CustName", CSName);
                    lstParams.Add("@CustAddress", CSAddress);

                    try{
                        int xqry = ExecQuery(qry, lstParams);
                        if (xqry < 1){
                            result = "failed";
                            //RollBack action here
                            int RollBack = RollBackUpdate(ID);
                            break;
                        }
                        else if ((idx == (csLen - 1)) && !(result == "failed")){
                            result = "success";
                        }
                    }catch (Exception ex){
                        string errMsg = ex.Message;
                    }
                }
            }else{
                result = "Update data failed";
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static string SaveProducts(string ID, string Distributor, string ProductDivision,
            List<object> ProductList)
        {
            string result = "";

            List<object> tmp = DeletePrevData(ID, "tbl_RequestProduct");
            if ((int)tmp[0] > 0) { 
                int ListLen = ProductList.Count;
                for (int idx = 0; idx < ListLen; idx++){
                    Dictionary<string, object> dct = (Dictionary<string, object>)ProductList[idx];
                    string ProductID = dct["ProdID"].ToString();
                    //string ProductIDAzi = dct["ProdIDAzi"].ToString();
                    string ProductName = dct["ProdName"].ToString();
                    double ProductDisc = Convert.ToDouble(dct["ProdDisc"].ToString());
                    int ProductQTY = Convert.ToInt32(dct["ProdQTY"].ToString());

                    string qry = "INSERT INTO Ebiz.dbo.tbl_RequestProduct (CLNum, DistributorCode, " +
                        "ProductCode, ProdDivision, ProductName, Discount, Quantity) " +
                        "VALUES (@CLNum, @DistributorCode, " +
                        "@ProductCode, @ProdDivision, @ProductName, @Discount, @Quantity)";
                    Dictionary<string, object> lstParams = new Dictionary<string, object>();
                    lstParams.Add("@CLNum", ID);
                    lstParams.Add("@DistributorCode", Distributor);
                    lstParams.Add("@ProductCode", ProductID);
                    lstParams.Add("@ProdDivision", ProductDivision);
                    lstParams.Add("@ProductName", ProductName);
                    lstParams.Add("@Discount", ProductDisc);
                    lstParams.Add("@Quantity", ProductQTY);

                    if (ExecQuery(qry, lstParams) < 1){
                        //result = qry;
                        //RollBack action here
                        result = "failed!";
                        int RollBack = RollBackUpdate(ID);
                        break;
                    }else if ((idx == (ListLen - 1)) && !(result == "failed")){
                        result = "success";
                    }
                }
            }else{
                result = "Update data failed";
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static Dictionary<string, object> SaveApprovals(string ID, List<object> ApprovalList)
        {

            Dictionary<string, object> result = new Dictionary<string, object>();
            List<object> tmp = DeletePrevData(ID, "tbl_RequestApproval");
            if ((int)tmp[0] > 0) {
                string Token = Func.GetUserInfo(Func.ShowInfo._SSID);

                Dictionary<string, object> dctSYS = Func.GetSysConfig();
                string SYSPort = "/";
                string SYSUrl = dctSYS["ServerAddress"].ToString() + SYSPort;
                int ListLen = ApprovalList.Count;
                for (int idx = 0; idx < ListLen; idx++){
                    Dictionary<string, object> dct = (Dictionary<string, object>)ApprovalList[idx];
                    string StepSeq = dct["StepNum"].ToString();
                    string CurrentStep = dct["CStep"].ToString();
                    string NextStep = dct["NStep"].ToString();
                    string EmailAddress = GetEmail(CurrentStep);
                    string EmailDate = "NULL";
                    string TokenExpiry = "NULL";

                    int EmailSentApp = 0;
                    int EmailSentReq = 0;
                    string PageQry = "id=" + ID + "&atdn=" + Token;
                    string PageLink = "";
                    if (idx == 0){
                        string PageQryMain = PageQry + "&user=" + CurrentStep + "&auto=true";
                        PageLink = SYSUrl + "ViewRequest.aspx?cl=" + CryptLib.Base64(PageQryMain, CryptMode.ENCRYPT);

                        Dictionary<string, object> SendAppEmail = SendEmail(CurrentStep, ID, PageLink, EmailMode.REQUEST, EmailEngine.NETMAIL, false);
                        EmailSentApp = Convert.ToInt32(SendAppEmail["Status"]);
                        if (EmailSentApp == 0){
                            result["Status"] = 0;
                            result["Message"] = SendAppEmail["Message"];

                            //RollBack action here
                            int RollBack = RollBackUpdate(ID);
                            break;
                        }else{
                            string _Requester = Func.GetUserInfo(Func.ShowInfo._UserID);
                            string PageQrySub = PageQry + "&user=" + _Requester + "&auto=true";
                            PageLink = SYSUrl + "ViewRequest.aspx?cl=" + CryptLib.Base64(PageQrySub, CryptMode.ENCRYPT);

                            Dictionary<string, object> SendReqEmail = SendEmail(CurrentStep, ID, PageLink, EmailMode.REQUEST, EmailEngine.NETMAIL, true);
                            EmailSentReq = Convert.ToInt32(SendReqEmail["Status"]);
                            if (EmailSentReq == 0){
                                result["Status"] = 0;
                                result["Message"] = SendReqEmail["Message"];

                                //RollBack action here
                                int RollBack = RollBackUpdate(ID);
                                break;
                            }else{
                                EmailDate = "'" + DateTime.Today.ToString("yyyy/MM/dd hh:mm:ss") + "'";
                                TokenExpiry = "'" + DateTime.Now.AddDays(7).ToString("yyyy/MM/dd hh:mm:ss") + "'";
                            }
                        }
                    }

                    string qry = "INSERT INTO Ebiz.dbo.tbl_RequestApproval (CLNum, StepSeq, CurrentStep, " +
                        "NextStep, EmailAddress, EmailDate, EmailSent, Token, TokenExpiry) VALUES ('" +
                        ID + "', " + StepSeq + ", '" + CurrentStep + "', '" + NextStep + "', '" +
                        EmailAddress + "', " + EmailDate + ", " + EmailSentReq + ", '" + Token + "', " +
                        TokenExpiry + ")";

                    if (ExecQuery(qry) < 1){
                        result["Status"] = 0;
                        result["Message"] = "Update New Request Failed!";

                        //RollBack action here
                        int RollBack = RollBackUpdate(ID);
                        break;
                    }else if (idx == (ListLen - 1)){
                        result["Status"] = 1;
                        result["Message"] = "Update New Request Success!";
                    }
                }
            }else{
                result["Status"] = 0;
                result["Message"] = "Update Data Failed!";
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<object> DeleteFile(string ID, string IDFile, string FileName,
            string PrevQueue, string PrevSize)
        {
            List<object> result = new List<object>();
            string path = HttpContext.Current.Server.MapPath("~/images/upload/" + FileName);
            FileInfo file = new FileInfo(path);
            if (file.Exists){
                string dbName = DBUtilities.GetDBName("devCon");
                string qry = "DELETE FROM " + dbName + ".dbo.tbl_RequestFiles WHERE " +
                    "(CLNum = @CLNum) AND (IDSeq = @IDSeq)";
                SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
                using (cn) {
                    try {
                        cn.Open();

                        SqlCommand cmd = new SqlCommand(qry, cn);
                        cmd.Parameters.AddWithValue("@CLNum", ID);
                        cmd.Parameters.AddWithValue("@IDSeq", IDFile);
                        if (cmd.ExecuteNonQuery() > 0) {
                            file.Delete();
                            result.Add(1);
                            result.Add("File Deleted");
                            double DBMaxSize = GetMaxFileSize(ID);
                            double _PrevSize = Convert.ToDouble(PrevSize);
                            double NewSize = _PrevSize - (_PrevSize - DBMaxSize);
                            int DBQueued = GetFileQueued(ID);
                            int _PrevQueue = Convert.ToInt32(PrevQueue);
                            int NewQueue = _PrevQueue - (_PrevQueue - DBQueued);
                            result.Add(NewSize);
                            result.Add(NewQueue);
                        }
                    }catch(Exception ex){
                        string ErrMsg = ex.Message;
                        result.Add(0);
                        result.Add(ErrMsg);
                    }finally{
                        if (cn.State == ConnectionState.Open) {
                            cn.Close();
                        }
                    }
                }
            }else{
                result.Add(0);
                result.Add("File Not Exist");
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static Dictionary<string, object> AttachFiles(string ID, List<object> FileList){

            Dictionary<string, object> result = new Dictionary<string, object>();
            string FileLocation = "images/upload/";
            int ListLen = FileList.Count;
            for (var idx = 0; idx < ListLen; idx++){
                Dictionary<string, object> dct = (Dictionary<string, object>)FileList[idx];
                string FileName = dct["FileName"].ToString();
                string[] _FileType = (dct["FileType"].ToString()).Split('.');
                string FileType = _FileType[1];
                string FileSize = dct["FileSize"].ToString();

                string qry = "INSERT INTO Ebiz.dbo.tbl_RequestFiles (CLNum, FileLocation, FileName, " +
                    "FileType, FileSize) VALUES " +
                    "(@CLNum, @FileLocation, @FileName, @FileType, @FileSize)";
                Dictionary<string, object> dctParams = new Dictionary<string, object>();
                dctParams.Add("@CLNum", ID);
                dctParams.Add("@FileLocation", FileLocation);
                dctParams.Add("@FileName", FileName);
                dctParams.Add("@FileType", FileType);
                dctParams.Add("@FileSize", FileSize);

                if (ExecQuery(qry, dctParams) < 1){
                    result["Status"] = 0;
                    result["Message"] = "Update Request Failed!";
                    result["Goto"] = "Home.aspx";

                    //Rollback Action
                    int RollBack = RollBackUpdate(ID);
                    break;
                }else if (idx == (ListLen - 1)){
                    string Token = Func.GetUserInfo(Func.ShowInfo._SSID);
                    string PageLink = "Home.aspx";

                    result["Status"] = 1;
                    result["Message"] = "Update Request Success!\nPlease check your email.";
                    result["Goto"] = PageLink;
                }
            }
            return result;
        }
    }
}
